<?php

/**
 * 合同收入信息仓库
 * @author wangwx 2015-5-11
 */
namespace common\repositories\myhtsq;

use common\entities\ConfigEntity;
use common\entities\myhtsq\ContractRevenueCarryoverEntity;
use common\repositories\BaseRepository;
use common\support\StringHelper;
use common\entities\myhtsq\ContractRevenueEntity;
use common\entities\myhtsq\CarryoverTimeEntity;
use yii\db\Query;
use common\support\Helper;
use common\entities\myhtsq\ContractReceivableEntity;

class ContractRevenueRepository extends BaseRepository
{

    /**
     * 构造方法
     * @param unknown $config (optional)
     */
    public function __construct($config = [])
    {
        parent::__construct($config);
    }

    /**
     * 根据合同id,获取收入信息
     * @param string $contractId 合同id
     * @return array
     */
    public static function getContractRevenueListByContractId($contractId)
    {
        $sql = "SELECT
                t.type_name,
                t.finance_type_name,
                CASE t.confirm_type WHEN 1 THEN '按租期确认' ELSE '一次性确认' END confirm_type_name,
                r.*
                FROM myscrm_contract_revenue r
                left join myscrm_buss_account_types t on r.revenue_type=t.type_id
                where contract_id='".$contractId."'";
        $data = ConfigEntity::getDb()->createCommand($sql)->queryAll();
        return $data;
    }

    /**
     * 根据收入id 删除收入信息
     * @param string $revenueId
     * @return boolean
     */
    public static function deleteContractRevenueByRevenueId($revenueId)
    {
        $entity = ContractRevenueEntity::find()->where(['revenue_id' => $revenueId])->one();
        $before = $entity?$entity->getAttributes():false;
        $res = ContractRevenueEntity::deleteAll(['revenue_id' => $revenueId]);
        IncomeItemLogRepository::add($before,false,$res,IncomeItemLogRepository::OPERATE_DELETE);
        return $res;
    }

    /**
     * 根据合同id 删除收入信息
     * @param string $contractid
     * @return boolean
     */
    public static function deleteContractRevenueByContractId($contractid)
    {
        return ContractRevenueEntity::deleteAll(['contract_id' => $contractid]);
    }

    /**
     * 新增收入信息
     * @param \common\models\myhtsq\ContractRevenueModel $contractRevenueModel 前端模型
     * @param string $contractId 合同id
     * @return boolean
     */
    public static function addContractRevenueRecord($contractRevenueModel, $contractId)
    {
        $contractRevenueEntity = new ContractRevenueEntity();
        $data = $contractRevenueModel->attributes;
        $contractRevenueEntity->load($data, '');
        $contractRevenueEntity->revenue_id = StringHelper::uuid();
        $contractRevenueEntity->contract_id = $contractId;
        $contractRevenueEntity->income_code = $contractRevenueModel->income_code;
        $now = $contractRevenueEntity?$contractRevenueEntity->getAttributes():false;

        $check = $contractRevenueEntity->generateCheck(ContractRevenueEntity::OPERATE_ADD);
        if(!$check){
            throw new \Exception('待生成的收入项不满足生成条件');
        }

        if ($contractRevenueEntity->save()) {
            IncomeItemLogRepository::add(false,$now,true,IncomeItemLogRepository::OPERATE_ADD);
            return $contractRevenueEntity->revenue_id;
        } else {
            IncomeItemLogRepository::add(false,$now,false,IncomeItemLogRepository::OPERATE_ADD);
            return false;
        }
    }

    /**
     * 更收入信息
     * @param \common\models\myhtsq\ContractRevenueModel $contractRevenueModel 前端模型
     * @return boolean
     */
    public static function updateContractRevenueRecord($contractRevenueModel)
    {

        $contractRevenueEntity = new ContractRevenueEntity();

        $contractModelInfo = $contractRevenueEntity::find()->where(['revenue_id' => $contractRevenueModel->revenue_id])->one();
        $before = $contractModelInfo?$contractModelInfo->getAttributes():false;
        $data = $contractRevenueModel->attributes;

        $contractModelInfo->load($data, '');

        $check = $contractModelInfo->generateCheck(ContractRevenueEntity::OPERATE_EDIT);
        if(!$check){
            throw new \Exception('待修改的收入项不满足修改条件');
        }

        if ($contractModelInfo->save()) {
            $now = $contractModelInfo->getAttributes();
            IncomeItemLogRepository::add($before,$now,true,IncomeItemLogRepository::OPERATE_EDIT);
            return $contractModelInfo->revenue_id;
        } else {
            $now = $contractModelInfo->getAttributes();
            IncomeItemLogRepository::add($before,$now,false,IncomeItemLogRepository::OPERATE_EDIT);
            return false;
        }
    }

    /**
     * 根据合同id,获取合同收入金额总额
     * @param string $contractId 合同id
     * @return float
     */
    public static function getContractRevenueSumByContractId($contractId)
    {
        return ContractRevenueEntity::find()->where(['contract_id' => $contractId])->sum('amount');
    }

    /**
     * 获取收入项累计已确认金额
     * @param $revenueId
     * @return mixed
     */
    static public function getTotalConfirmedAmount($revenueId)
    {
        return floatval(ContractRevenueCarryoverEntity::find()->where(['revenue_id' => $revenueId])->sum('this_period_confirm_amount'));
    }

    /**
     * 获取指定收入项最近一次转结记录
     * @param $revenueId
     * @return array|null|\yii\db\ActiveRecord
     */
    static public function getLatestCarryoverByRevenueId($revenueId){
        return ContractRevenueCarryoverEntity::find()->where(array('revenue_id'=>$revenueId))->orderBy('carryover_end_time desc')->one();
    }

    /**
     * 根据时间获取收入数据
     * @param type $beginTime
     * @param type $endTime
     * @return type
     */
    public static function getRevenueInfotByTime($beginTime, $endTime)
    {
        $sql = "select distinct r.revenue_id, r.income_code, t.type_name, r.amount, c.BeginOn, c.EndOn, m.sign_date, m.contract_code as ContractCode, m.input_date, r.contract_id " .
            "from myscrm_contract_revenue as r " .
            "inner join myscrm_contract_manage as m on r.contract_id=m.contract_id " .
            "left join myscrm_buss_account_types as t on r.revenue_type=t.type_id " .
            "left join myscrm_contract as c on m.contract_code=c.ContractNum where m.is_cancelled=0 ";

        if (!empty($beginTime) && !empty($endTime)) {
            $sql .= "and m.input_date>:beginTime and m.input_date< :endTime";
        }
        $data = ContractRevenueEntity::getDb()->createCommand($sql, [':beginTime' => $beginTime, ':endTime' => $endTime])->queryAll();
        return $data;
    }

    /**
     * 修改收入编码
     * @param string $contractId 合同ID
     * @param string $incomeCode 收入编码
     * @return unknown
     */
    public static function updateIncomeCodeByContractId($contractId, $incomeCode)
    {
        $sql = "UPDATE myscrm_contract_revenue set income_code = CONCAT(:incomeCode,SUBSTR(income_code,INSTR(income_code,'-SR-'))) WHERE contract_id = :contractId";
        return ContractRevenueEntity::getDb()->createCommand($sql, [':incomeCode' => $incomeCode, ':contractId' => $contractId])->execute();
    }

    /**
     * @param $oid
     */
    public static function getContractRevenueById($oid){
        $sql = "SELECT DISTINCT
                r.revenue_id,
                r.income_code,
                t.type_name,
                t.confirm_type,
                if(t.confirm_type=1,'按租期','一次性') confirm_type_name,
                r.amount,
                r.carryover_begin_time,
                r.right_begin_time,
                r.right_end_time,
                r.total_confirm_amount,
                r.wait_confirm_amount,
                r.no_confirm_bad_amount,
                r.have_confirm_bad_amount,
                r.kingdie_verif_bad_amount,
                if(r.close_status=1,'已关闭','未关闭') close_status
                FROM myscrm_contract_revenue r
                LEFT JOIN myscrm_buss_account_types t ON r.revenue_type = t.type_id
                WHERE r.revenue_id=:revenue_id";
        $data = ContractRevenueEntity::getDb()
            ->createCommand($sql,[':revenue_id'=>$oid])
            ->queryOne();
        return $data;
    }

    /**
     * 获取应收款列表
     * @param $incometype
     * @return array
     */
    public static function getIncomeList($incometype,$pageIndex,$pageSize,$keyword,$confirmtype,$incomeresource,$usetype='list'){


        $carryoverbgtime = '';
        $carryoverendtime = '';
        $carryovertime = CarryoverTimeEntity::find()->one();
        if(!empty($carryovertime)){
            $carryoverbgtime = $carryovertime->carryover_begin_time;
            $carryoverendtime = $carryovertime->carryover_end_time;
        }
        $sql = "SELECT
                m.contract_id,
                m.contract_code,
                m.customer_name,
                m.activity_name,
                m.supplier_name,
                m.business_type_id,
                if(IFNULL(m.activity_name,'')='',if(IFNULL(m.supplier_name,'')='',m.customer_name,m.supplier_name),m.activity_name) cstname,
                m.business_type_id,
                m.area_id,
                m.input_date,
                m.sign_date,
                ac.account_company_name,
                m.sign_people_id,
                us.UserName,
                m.customer_full_name,
                p.application_name,
                r.amount,
                r.total_confirm_amount,
                r.wait_confirm_amount,
                r.income_code,
                r.carryover_begin_time,
                r.right_begin_time,
                r.right_end_time,
                t.type_name,
                t.confirm_type,
                trc.this_period_confirm_amount,
                if(t.confirm_type=1,'按租期','一次性') confirm_type_name,
                u.BUName as area_name,
                if(r.income_source=1,'上期结转','本期新增') income_source
                FROM myscrm_contract_revenue r
                inner join myscrm_contract_manage m on r.contract_id=m.contract_id AND m.contract_status=2 AND m.is_cancelled != 1
                inner join myscrm_buss_account_types t on r.revenue_type=t.type_id
                LEFT JOIN (
                    select DISTINCT(myscrm_contract_revenue_carryover.revenue_id),this_period_confirm_amount FROM myscrm_contract_revenue_carryover 
                    LEFT JOIN myscrm_contract_revenue ON myscrm_contract_revenue.revenue_id = myscrm_contract_revenue_carryover.revenue_id
                    GROUP BY myscrm_contract_revenue_carryover.revenue_id ORDER BY carryover_end_time DESC
                ) trc ON r.revenue_id = trc.revenue_id
                left join myscrm_erp_business_unit u on m.area_id=u.BUGUID
                left join myscrm_erp_user us on us.UserGUID=m.sign_people_id
                left join myscrm_contract_payover p on r.contract_payover_id=p.contract_payover_id
                left join myscrm_contract_account_company ac on m.account_company_id=ac.account_company_id ";

        $sqlcount = "SELECT
                COUNT(1)
                FROM myscrm_contract_revenue r
                inner join myscrm_contract_manage m on r.contract_id=m.contract_id AND m.contract_status=2 AND m.is_cancelled != 1
                inner join myscrm_buss_account_types t on r.revenue_type=t.type_id
                left join myscrm_erp_business_unit u on m.area_id=u.BUGUID
                left join myscrm_erp_user us on us.UserGUID=m.sign_people_id
                left join myscrm_contract_payover p on r.contract_payover_id=p.contract_payover_id
                left join myscrm_contract_account_company ac on m.account_company_id=ac.account_company_id";

        if($incometype == 'incomehaveconfirm'){
            $sql = "SELECT
                m.contract_id,
                m.contract_code,
                m.customer_name,
                m.activity_name,
                m.supplier_name,
                m.business_type_id,
                IF (IFNULL(m.activity_name, '') = '',IF (IFNULL(m.supplier_name, '') = '',m.customer_name,m.supplier_name),m.activity_name) cstname,
                m.business_type_id,
                m.area_id,
                m.input_date,
                m.sign_date,
                ac.account_company_name,
                m.sign_people_id,
                us.UserName,
                m.customer_full_name,
                p.application_name,
                r.amount,
                r.total_confirm_amount,
                r.wait_confirm_amount,
                r.income_code,
                r.carryover_begin_time,
                r.right_begin_time,
                r.right_end_time,
                t.type_name,
                t.confirm_type,
                tmcrc.revenue_source,
                IF (tmcrc.revenue_source > 0,'上期结转','本期新增') income_source,
                c.this_period_confirm_amount,
                IF (t.confirm_type = 1,'按租期','一次性') confirm_type_name,
                u.BUName AS area_name
                FROM
                 myscrm_contract_revenue_carryover c
                INNER JOIN myscrm_contract_revenue r ON c.revenue_id = r.revenue_id
                INNER JOIN myscrm_contract_manage m ON r.contract_id = m.contract_id
                AND m.contract_status = 2
                AND m.is_cancelled != 1
                INNER JOIN myscrm_buss_account_types t ON r.revenue_type = t.type_id
                LEFT JOIN (
                 SELECT
                  count(1) AS revenue_source,
                  revenue_id
                 FROM
                  myscrm_contract_revenue_carryover
                 WHERE
                  carryover_end_time < '$carryoverendtime'
                 GROUP BY
                  revenue_id
                ) tmcrc ON tmcrc.revenue_id = r.revenue_id
                LEFT JOIN myscrm_erp_business_unit u ON m.area_id = u.BUGUID
                LEFT JOIN myscrm_erp_user us ON us.UserGUID = m.sign_people_id
                LEFT JOIN myscrm_contract_payover p ON r.contract_payover_id = p.contract_payover_id
                LEFT JOIN myscrm_contract_account_company ac ON m.account_company_id = ac.account_company_id
                INNER JOIN `myscrm_contract_property` `cp` ON cp.property_id = m.contract_property_id";

            $sqlcount = "SELECT
                COUNT(1)
                FROM myscrm_contract_revenue_carryover c
                INNER JOIN myscrm_contract_revenue r ON c.revenue_id = r.revenue_id
                inner join myscrm_contract_manage m on r.contract_id=m.contract_id AND m.contract_status=2 AND m.is_cancelled != 1
                inner join myscrm_buss_account_types t on r.revenue_type=t.type_id
                left join myscrm_erp_business_unit u on m.area_id=u.BUGUID
                left join myscrm_erp_user us on us.UserGUID=m.sign_people_id
                left join myscrm_contract_payover p on r.contract_payover_id=p.contract_payover_id
                left join myscrm_contract_account_company ac on m.account_company_id=ac.account_company_id
                INNER JOIN `myscrm_contract_property` `cp` ON cp.property_id = m.contract_property_id";
        }

        $sqlwhere = ' WHERE r.amount<>0 ';
        //关键字
        if(!empty($keyword)){
            $sqlwhere .= " AND (m.customer_name like '%".$keyword."%' or m.activity_name like '%".$keyword."%' or m.supplier_name like '%".$keyword."%' or r.income_code like '%".$keyword."%')";
        }
        if($confirmtype != ''){
            $sqlwhere .= ' AND t.confirm_type='.$confirmtype;
        }

        if(!empty($incometype)){
            //待确认
            if($incometype == 'incomewaitconfirm'){
                if($incomeresource != ''){
                    $sqlwhere .= ' AND r.income_source='.$incomeresource;
                }
                $sqlwhere .= ' AND r.close_status<>1';
                if(empty($carryoverbgtime)){
                    $sqlwhere .= ' AND (1=2)';
                }else{
                    $sqlwhere .= " AND ((m.contract_status=2 AND r.carryover_begin_time<='" . $carryoverendtime . "') or (r.wait_confirm_amount!=0 and r.revenue_id in (SELECT revenue_id FROM myscrm_contract_revenue_carryover)))";
                }
            }
            //本期已确认
            if($incometype == 'incomehaveconfirm'){
                $sqlwhere .= ' AND ( `cp`.`property_name` NOT LIKE \'明源签约%\' ) ';
                if(empty($carryoverbgtime)){
                    $sqlwhere .= ' AND (1=2)';
                }else{
                    $sqlwhere .= " AND c.carryover_begin_time = '$carryoverbgtime' AND carryover_end_time = '$carryoverendtime'";
                }
            }
            //已关闭
            if($incometype == 'incomehaveclose'){
                if($incomeresource != ''){
                    $sqlwhere .= ' AND r.income_source='.$incomeresource;
                }
                $sqlwhere .= ' AND r.close_status=1';
            }
        }

        $sql .= $sqlwhere;
        $sqlcount .= $sqlwhere;

        $orderby = 'r.created_on desc,r.revenue_id';

        //导出不需要分页
        if($usetype == 'export'){
            $sql = $sql . ' ORDER BY ' . $orderby;
        }else {
            $offset = $pageSize * ($pageIndex - 1);
            $limit = $pageSize;

            $sql = $sql . ' ORDER BY ' . $orderby . ' limit ' . $limit . ' offset ' . $offset;
        }

        $data = ContractRevenueEntity::getDb()
            ->createCommand($sql)
            ->queryAll();

        $count = ContractRevenueEntity::getDb()
            ->createCommand($sqlcount)
            ->queryScalar();
        if(empty($count)){
            $count = 0;
        }

        return Helper::setPageResult($count, $data);
    }

    /**
     * 获取应收结转数据
     * @param $stype    0一次性  1按租期   9全部
     * @param $revenueid
     * @return array
     */
    public static function getCarryoverData($stype){
        $sql = "SELECT
        r.contract_id,
        r.revenue_id,
        r.total_confirm_amount,
        r.amount,
        r.right_begin_time,
        r.right_end_time,
        t.confirm_type,
        r.carryover_begin_time revbgtime,
        tm.carryover_begin_time,
        tm.carryover_end_time,
        IFNULL(cc.this_period_confirm_amount,0) co_this_period_confirm_amount,
		cc.carryover_id
        FROM myscrm_contract_revenue r
        inner join myscrm_contract_manage m on r.contract_id=m.contract_id AND m.contract_status=2 AND m.is_cancelled != 1
        inner join myscrm_buss_account_types t on r.revenue_type=t.type_id
        inner join myscrm_carryover_time tm
        left join myscrm_contract_revenue_carryover cc on cc.revenue_id = r.revenue_id AND tm.carryover_begin_time=cc.carryover_begin_time AND tm.carryover_end_time=cc.carryover_end_time
        ";

        $sqlwhere = 'where (r.carryover_begin_time<=tm.carryover_end_time or (r.wait_confirm_amount!=0 and r.revenue_id in (SELECT revenue_id FROM myscrm_contract_revenue_carryover)))';
        $sqlwhere .= ' and r.close_status<>1 AND r.amount<>0';
        if(!empty($stype)){
            if($stype == 0 || $stype == 1){
                $sqlwhere .= " and t.confirm_type=".$stype;
            }
        }
        $sql .= $sqlwhere;
        $data = ContractRevenueEntity::getDb()
            ->createCommand($sql)
            ->queryAll();

        return $data;
    }

    /**
     * 根据收入ID获取最新的结转数据
     */
    public static function getLatestCarroverByReveId($revenueid){
        return ContractRevenueCarryoverEntity::find()->where(['revenue_id'=>$revenueid])->orderBy('created_on desc')->limit(1)->one();
    }

    /**
     * 保存结转信息,同事更新应收款对应收据
     * @param $revenueid
     * @param $carryoverbegintime
     * @param $carryoverendtime
     * @param $thisperiodconfirmamount
     * @param $thisperiodconfirmtotalamount
     * @param $lastperiodtotalconfirmamount
     * @param $totalconfirmamount
     * @param $waitconfirmamount
     * @return bool
     */
    public static function saveCarryoverData($revenueid,$carryoverbegintime,$carryoverendtime,$thisperiodconfirmamount,$thisperiodconfirmtotalamount,$lastperiodtotalconfirmamount,$waitconfirmamount){
        $newcarryover = new ContractRevenueCarryoverEntity();
        $newcarryover->carryover_id = StringHelper::uuid();
        $newcarryover->revenue_id = $revenueid;
        $newcarryover->carryover_begin_time = $carryoverbegintime;
        $newcarryover->carryover_end_time = $carryoverendtime;
        $newcarryover->last_period_total_confirm_amount = $lastperiodtotalconfirmamount;
        $newcarryover->this_period_confirm_amount = $thisperiodconfirmamount;
        $newcarryover->this_period_confirm_total_amount = $thisperiodconfirmtotalamount;
        $newcarryover->created_on = ConfigEntity::getDbNowDate();

        //检查当前收入项是否满足转结条件
        $newrevenue = ContractRevenueEntity::find()->where(['revenue_id'=>$revenueid])->one();
        $check = $newrevenue->carryOverCheck($newcarryover->getAttributes());
        if(!$check){
            return false;
        }

        if($newcarryover->save()){
            //保存成功后更新应收款
            $before = $newrevenue?$newrevenue->getAttributes():false;
            $newrevenue->total_confirm_amount = $thisperiodconfirmtotalamount;
            $newrevenue->wait_confirm_amount = $waitconfirmamount;
            $newrevenue->close_status = $waitconfirmamount==0?1:0;
            $newrevenue->income_source = 1;
            $now = $newrevenue->getAttributes();
            //检查当前收入项
            if($newrevenue->closeCheck()){
                $res = $newrevenue->save();
            }else{
                return false;
            }
            IncomeItemLogRepository::add($before,$now,$res,IncomeItemLogRepository::OPERATE_EDIT);
            return $res;
        }else {
            return false;
        }
    }

    /**
     * 获取合同下是否有结转数据
     * @param $contractid
     * @return false|null|string
     */
    public static function isHaveCarryover($contractid,$contractpayoverid){
        $sql = "SELECT COUNT(1) FROM myscrm_contract_revenue r
            INNER JOIN myscrm_contract_revenue_carryover c on r.revenue_id=c.revenue_id
            WHERE r.contract_id='".$contractid."' and r.contract_payover_id='".$contractpayoverid."'";

        $count = ContractRevenueEntity::getDb()->createCommand($sql)->queryScalar();
        if($count > 0){
            return true;
        }else{
            return false;
        }
    }

    /**
     * @param $contractid
     * @param $oid
     * @return int
     */
    public static function deleteRevenueByCP($contractid,$oid){
        $entity = ContractRevenueEntity::find()->where(['contract_id'=>$contractid,'contract_payover_id'=>$oid])->one();
        $before = $entity?$entity->getAttributes():false;
        $res = ContractRevenueEntity::deleteAll(['contract_id'=>$contractid,'contract_payover_id'=>$oid]);
        IncomeItemLogRepository::add($before,false,$res,IncomeItemLogRepository::OPERATE_DELETE);
        return $res;
    }
}
